"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.
"""

"""
Case Type   : 资源池化
Case Name   : 资源池化场景下,备机prepare语句对物化视图的数据同步
Create At   : 2025-03-07
Owner       : zhongchengzhi
Description :
    1、查询集群状态
    2、主机创建基表并插入数据
    3、主机创建物化视图
    4、备机准备和执行prepare语句
    5、主机向物化视图插入数据
    6、备机执行prepare语句
    7、主机更新数据
    8、备机执行prepare语句
    9、主机删除数据
    10、备机执行prepare语句
    11、清理环境

Expect      :
    1、查询结果正常
    2、创建、插入数据成功
    3、创建物化视图成功
    4、备机执行成功
    5、合理报错
    6、备机执行成功
    7、合理报错
    8、备机执行成功
    9、合理报错
    10、备机执行成功
    11、清理环境成功
History     :
    1、2025-03-07 创建
    2、2025-07-12 改造用例为1p1s环境可执行
"""
import os
import unittest
import time

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import macro

Primary_ROOTSH = CommonSH('PrimaryRoot')
Primary_SH = CommonSH('PrimaryDbUser')
Standby1_SH = CommonSH('Standby1DbUser')

@unittest.skipIf(1 == Primary_SH.get_node_num(), '单机环境不执行')
class SharedStorage(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.s1_comsh = CommonSH('Standby1DbUser')

    def test_shared_storage(self):
        text = '-----step1:查询数据库集群状态-----'
        self.log.info(text)
        status = Primary_SH.exec_cm_ctl(mode='query', param='-Cvipd')
        self.log.info(status)
        self.assertIn('cluster_state   : Normal', status, '执行失败' + text)

        text = '-----step2:主机创建基表并插入数据;expect:成功-----'
        self.log.info(text)
        sql = f'''
                drop table if exists student cascade;
                create table student
                (
                    id int primary key,
                    name varchar(10) not null
                );
                insert into student values (1, 'aaa');
                insert into student values (2, 'bbb');
                insert into student values (3, 'ccc');
                insert into student values (4, 'ddd');
                insert into student values (5, 'eee');
                insert into student values (6, 'fff');
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('CREATE TABLE\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1\nINSERT 0 1', status, '执行失败' + text)

        text = '-----step3:主机创建物化视图;expect:创建物化视图成功-----'
        self.log.info(text)
        sql = f'''
                create materialized view student_mv (id, name) as select * from student;
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('CREATE MATERIALIZED VIEW', status, '执行失败' + text)

        text = '-----step4:备机准备和执行prepare语句;expect:执行成功-----'
        self.log.info(text)
        sql = f'''
                prepare standby1 as select * from student_mv;
                explain execute standby1(1);
                '''
        self.log.info(sql)
        status = Standby1_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('Seq Scan on student_mv  (cost=0.00..27.02 rows=1702 width=18)', status, '执行失败' + text)

        text = '-----step5:主机插入数据 expect:合理报错-----'
        self.log.info(text)
        sql = f"insert into student_mv values (7, 'ggg');"
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('ERROR:  Unsupported feature', status, '执行失败' + text)

        text = '-----step6:备机执行prepare语句;expect:执行成功-----'
        self.log.info(text)
        sql = f'''
                prepare standby1 as select * from student_mv;
                explain execute standby1(1);
                '''
        self.log.info(sql)
        status = Standby1_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('Seq Scan on student_mv  (cost=0.00..27.02 rows=1702 width=18)', status, '执行失败' + text)

        text = '-----step7:主机更新数据;expect:合理报错-----'
        self.log.info(text)
        sql = f"update student_mv set name = 'fff_new' where id = 1;"
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('ERROR:  Unsupported feature', status, '执行失败' + text)

        text = '-----step8:备机执行prepare语句;expect:执行报错-----'
        self.log.info(text)
        sql = f'''
                prepare standby1 as select * from student_mv;
                explain execute standby1(1);
                '''
        self.log.info(sql)
        status = Standby1_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('Seq Scan on student_mv  (cost=0.00..27.02 rows=1702 width=18)', status, '执行失败' + text)

        text = '-----step9:主机删除数据;expect:合理报错-----'
        self.log.info(text)
        sql = f"delete from student_mv where id = 1;"
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('ERROR:  Unsupported feature', status, '执行失败' + text)

        text = '-----step10:备机执行prepare语句;expect:执行报错-----'
        self.log.info(text)
        sql = f'''
                prepare standby1 as select * from student_mv;
                explain execute standby1(1);
                '''
        self.log.info(sql)
        status = Standby1_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('Seq Scan on student_mv  (cost=0.00..27.02 rows=1702 width=18)', status, '执行失败' + text)

        text = '-----step11:清理环境;expect:成功-----'
        self.log.info(text)
        sql = f'''
                drop table student cascade;
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('NOTICE:  drop cascades to materialized view student_mv\nDROP TABLE', status, '执行失败' + text)

    def tearDown(self):
        text = '-----step12:恢复环境为初始集群状态; expect:成功-----'
        self.log.info(text)
        status = Primary_SH.exec_cm_ctl(mode='stop')
        self.log.info(status)
        self.assertIn('cm_ctl: stop cluster successfully.', status, '执行失败' + text)

        status = Primary_SH.exec_cm_ctl(mode='start')
        self.log.info(status)
        self.assertIn('cm_ctl: start cluster successfully.', status, '执行失败' + text)

        status = Primary_SH.exec_cm_ctl(mode='switchover', param='-a')
        self.log.info(status)
        self.assertIn(self.constant.cm_switchover_success_msg, status, '执行失败' + text)

        status = Primary_SH.exec_cm_ctl(mode='query', param='-Cvipd')
        self.log.info(status)
        self.assertIn('cluster_state   : Normal', status, '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')